{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.\n",
    "SPDX-License-Identifier: Apache-2.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# English Premier League Teams and Stadiums\n",
    "This notebook uses an RDF graph containing the teams that took part in the 2019/20 English Premier League season. The graph includes information about the teams, their stadiums and the cities where they play.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check the status of our connection\n",
    "The three cells below can be used to check the version of the workbench, the current configuration, and the status of the Neptune cluster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%graph_notebook_version"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%graph_notebook_config"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%status"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the graph\n",
    "The cell below creates the RDF graph. A set of queries that can be run against the data follows in the subsequent cells."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "\n",
    "PREFIX rdf:  <http://www.w3.org/1999/02/22-rdf-syntax-ns#>\n",
    "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
    "PREFIX owl:  <http://www.w3.org/2002/07/owl#>\n",
    "PREFIX xsd:  <http://www.w3.org/2001/XMLSchema#>\n",
    "\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "prefix so:   <https://schema.org/>\n",
    "\n",
    "INSERT DATA\n",
    "{\n",
    "#\n",
    "# Teams\n",
    "#\n",
    "  soco:Team a so:SportsTeam .\n",
    "    \n",
    "  socr:Arsenal a soco:Team ; \n",
    "    rdfs:label \"Arsenal\" ;\n",
    "    soco:teamName \"Arsenal F.C.\" ;\n",
    "    soco:nickname \"The Gunners\" ;\n",
    "    soco:founded 1886 ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:homeStadium socr:The_Emirates .\n",
    "  socr:Chelsea a soco:Team ; \n",
    "    rdfs:label \"Chelsea\" ;\n",
    "    soco:teamName \"Chelsea F.C.\" ;\n",
    "    soco:nickname \"The Blues\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1905 ;\n",
    "    soco:homeStadium socr:Stamford_Bridge .\n",
    "  socr:Spurs a soco:Team ; \n",
    "    rdfs:label \"Tottenham Hotspur\" ;\n",
    "    soco:teamName \"Tottenham Hotspur F.C.\" ;\n",
    "    soco:nickname \"Spurs\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1882 ;\n",
    "    soco:homeStadium socr:Tottenham_Hotspur_Stadium .\n",
    "  socr:West_Ham a soco:Team ; \n",
    "    rdfs:label \"West Ham United\" ;\n",
    "    soco:teamName \"West Ham United F.C.\" ;\n",
    "    soco:nickname \"The Hammers\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1895 ;\n",
    "    soco:homeStadium socr:London_Stadium .\n",
    "  socr:Liverpool a soco:Team ;\n",
    "    rdfs:label \"Liverpool\" ;\n",
    "    soco:teamName \"Liverpool F.C.\" ;\n",
    "    soco:nickname \"The Reds\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1892 ;\n",
    "    soco:homeStadium socr:Anfield .\n",
    "  socr:Everton a soco:Team ;\n",
    "    rdfs:label \"Everton\" ;\n",
    "    soco:teamName \"Everton F.C.\" ;\n",
    "    soco:nickname \"The Toffees\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1878 ;\n",
    "    soco:homeStadium socr:Goodison_Park .\n",
    "  socr:Manchester_United a soco:Team ;\n",
    "    rdfs:label \"Manchester United\" ;\n",
    "    soco:teamName \"Manchester United F.C.\" ;\n",
    "    soco:nickname \"The Red Devils\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1878 ;\n",
    "    soco:homeStadium socr:Old_Trafford . \n",
    "  socr:Manchester_City a soco:Team ;\n",
    "    rdfs:label \"Manchester City\" ;\n",
    "    soco:teamName \"Manchester City F.C\" ;\n",
    "    soco:nickname \"The Citizens\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1880 ;\n",
    "    soco:homeStadium socr:The_Etihad . \n",
    "  socr:Wolves a soco:Team ;\n",
    "    rdfs:label \"Wolverhampton Wanderers\" ;\n",
    "    soco:teamName \"Wolverhampton Wanderers F.C\" ;\n",
    "    soco:nickname \"Wolves\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1877 ;\n",
    "    soco:homeStadium socr:Molineux . \n",
    "  socr:Burnley a soco:Team ;\n",
    "    rdfs:label \"Burnley\" ;\n",
    "    soco:teamName \"Burnley F.C\" ;\n",
    "    soco:nickname \"The Clarets\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1882 ;\n",
    "    soco:homeStadium socr:Turf_Moor . \n",
    "  socr:Sheffield_United a soco:Team ;\n",
    "    rdfs:label \"Sheffield United\" ;\n",
    "    soco:teamName \"Sheffield United F.C\" ;\n",
    "    soco:nickname \"The Blades\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1889 ;\n",
    "    soco:homeStadium socr:Bramall_Lane . \n",
    "  socr:Crystal_Palace a soco:Team ;\n",
    "    rdfs:label \"Crystal Palace\" ;\n",
    "    soco:teamName \"Crystal Palace F.C\" ;\n",
    "    soco:nickname \"Eagles\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1905 ;\n",
    "    soco:homeStadium socr:Selhurst_Park . \n",
    "  socr:Southampton a soco:Team ;\n",
    "    rdfs:label \"Southampton\" ;\n",
    "    soco:teamName \"Southampton F.C\" ;\n",
    "    soco:nickname \"The Saints\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1885 ;\n",
    "    soco:homeStadium socr:St_Marys . \n",
    "  socr:Newcastle_United a soco:Team ;\n",
    "    rdfs:label \"Newcastle United\" ;\n",
    "    soco:teamName \"Newcastle United F.C\" ;\n",
    "    soco:nickname \"The Magpies\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1892 ;\n",
    "    soco:homeStadium socr:St_James_Park . \n",
    "  socr:Brighton a soco:Team ;\n",
    "    rdfs:label \"Brighton and Hove Albion\" ;\n",
    "    soco:teamName \"Brighton and Hove Albion F.C\" ;\n",
    "    soco:nickname \"Seagulls\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1901 ;\n",
    "    soco:homeStadium socr:Amex_Stadium . \n",
    "  socr:Watford a soco:Team ;\n",
    "    rdfs:label \"Watford\" ;\n",
    "    soco:teamName \"Watford F.C.\" ;\n",
    "    soco:nickname \"Hornets\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1898 ;\n",
    "    soco:homeStadium socr:Vicarage_Road . \n",
    "  socr:Bournemouth a soco:Team ;\n",
    "    rdfs:label \"Bournemouth\" ;\n",
    "    soco:teamName \"Bournemouth F.C.\" ;\n",
    "    soco:nickname \"The Cherries\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1899 ;\n",
    "    soco:homeStadium socr:Vitality_Stadium . \n",
    "  socr:Aston_Villa a soco:Team ;\n",
    "    rdfs:label \"Aston Villa\" ;\n",
    "    soco:teamName \"Aston Villa F.C.\" ;\n",
    "    soco:nickname \"The Villans\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1897 ;\n",
    "    soco:homeStadium socr:Villa_Park . \n",
    "  socr:Leicester_City a soco:Team ;\n",
    "    rdfs:label \"Leicester City\" ;\n",
    "    soco:teamName \"Leicester City F.C.\" ;\n",
    "    soco:nickname \"The Foxes\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1884 ;\n",
    "    soco:homeStadium socr:King_Power_Stadium . \n",
    "  socr:Norwich_City a soco:Team ;\n",
    "    rdfs:label \"Norwich City\" ;\n",
    "    soco:teamName \"Norwich City F.C.\" ;\n",
    "    soco:nickname \"The Canaries\" ;\n",
    "    soco:currentLeague socr:EPL ;\n",
    "    soco:founded 1902 ;\n",
    "    soco:homeStadium socr:Carrow_Road_Stadium . \n",
    "#\n",
    "#\n",
    "# Stadiums\n",
    "#\n",
    "  soco:Stadium a so:StadiumOrArena .\n",
    "    \n",
    "  socr:The_Emirates a soco:Stadium ;\n",
    "    rdfs:label \"The Emirates\" ;\n",
    "    soco:capacity 60704 ;\n",
    "    soco:opened 2006 ;\n",
    "    so:location locr:London .\n",
    "  socr:Stamford_Bridge a soco:Stadium ;\n",
    "    rdfs:label \"Stamford Bridge\" ;\n",
    "    soco:capacity 40834 ;\n",
    "    soco:opened 1877 ;\n",
    "    so:location locr:London .\n",
    "  socr:Tottenham_Hotspur_Stadium a soco:Stadium ;\n",
    "    rdfs:label \"Tottenham Hotspur Stadium\" ;\n",
    "    soco:capacity 62214 ;\n",
    "    soco:opened 2019 ;\n",
    "    so:location locr:London .\n",
    "  socr:London_Stadium a soco:Stadium ;\n",
    "    rdfs:label \"London Stadium\" ;\n",
    "    soco:capacity 60000 ;\n",
    "    soco:opened 2016 ;\n",
    "    so:location locr:London .\n",
    "  socr:Anfield a soco:Stadium ;\n",
    "    rdfs:label \"Anfield\" ;\n",
    "    soco:capacity 53394 ;\n",
    "    soco:opened 1884 ;\n",
    "    so:location locr:Liverpool .\n",
    "  socr:Goodison_Park a soco:Stadium ;\n",
    "    rdfs:label \"Goodison Park\" ;\n",
    "    soco:capacity 39414 ;\n",
    "    soco:opened 1892 ;\n",
    "    so:location locr:Liverpool .\n",
    "  socr:Old_Trafford a soco:Stadium ;\n",
    "    rdfs:label \"Old Trafford\" ;\n",
    "    soco:capacity 75643 ;\n",
    "    soco:opened 1910 ;\n",
    "    so:location locr:Manchester .\n",
    "  socr:The_Etihad a soco:Stadium ;\n",
    "    rdfs:label \"Etihad Stadium\" ;\n",
    "    soco:capacity 55107 ;\n",
    "    soco:opened 2003 ;\n",
    "    so:location locr:Manchester .\n",
    "  socr:Molineux a soco:Stadium ;\n",
    "    rdfs:label \"Molineux Stadium\" ;\n",
    "    soco:capacity 32050 ;\n",
    "    soco:opened 1889 ;\n",
    "    so:location locr:Wolverhampton .\n",
    "  socr:Turf_Moor a soco:Stadium ;\n",
    "    rdfs:label \"Turf Moor\" ;\n",
    "    soco:capacity 21944 ;\n",
    "    soco:opened 1883 ;\n",
    "    so:location locr:Burnley .\n",
    "  socr:Bramall_Lane a soco:Stadium ;\n",
    "    rdfs:label \"Bramall Lane\" ;\n",
    "    soco:capacity 32125 ;\n",
    "    soco:opened 1855 ;\n",
    "    so:location locr:Sheffield .\n",
    "  socr:Selhurst_Park a soco:Stadium ;\n",
    "    rdfs:label \"Selhurst Park Stadium\" ;\n",
    "    soco:capacity 25486 ;\n",
    "    soco:opened 1924 ;\n",
    "    so:location locr:London .\n",
    "  socr:St_Marys a soco:Stadium ;\n",
    "    rdfs:label \"St. Mary's Stadium\" ;\n",
    "    soco:capacity 32384 ;\n",
    "    soco:opened 2001 ;\n",
    "    so:location locr:Southampton .\n",
    "  socr:St_James_Park a soco:Stadium ;\n",
    "    rdfs:label \"St. James' Park\" ;\n",
    "    soco:capacity 52305 ;\n",
    "    soco:opened 1880 ;\n",
    "    so:location locr:Newcastle .\n",
    "  socr:Amex_Stadium a soco:Stadium ;\n",
    "    rdfs:label \"American Express Community Stadium\" ;\n",
    "    soco:capacity 30750 ;\n",
    "    soco:opened 2011 ;\n",
    "    so:location locr:Brighton .\n",
    "  socr:Vicarage_Road a soco:Stadium ;\n",
    "    rdfs:label \"Vicarage Road\" ;\n",
    "    soco:capacity 22200 ;\n",
    "    soco:opened 1922 ;\n",
    "    so:location locr:Watford .\n",
    "  socr:Vitality_Stadium a soco:Stadium ;\n",
    "    rdfs:label \"Vitality Stadium\" ;\n",
    "    soco:capacity 11364 ;\n",
    "    soco:opened 1910 ;\n",
    "    so:location locr:Bournemouth .\n",
    "  socr:Villa_Park a soco:Stadium ;\n",
    "    rdfs:label \"Villa Park\" ;\n",
    "    soco:capacity 42095 ;\n",
    "    soco:opened 1897 ;\n",
    "    so:location locr:Birmingham .\n",
    "  socr:King_Power_Stadium a soco:Stadium ;\n",
    "    rdfs:label \"King Power Stadium\" ;\n",
    "    soco:capacity 32261 ;\n",
    "    soco:opened 2002 ;\n",
    "    so:location locr:Leicester .\n",
    "  socr:Carrow_Road_Stadium a soco:Stadium ;\n",
    "    rdfs:label \"Carrow Road Stadium\" ;\n",
    "    soco:capacity 27359 ;\n",
    "    soco:opened 1935 ;\n",
    "    so:location locr:Norwich .\n",
    "    \n",
    "#\n",
    "# Cities\n",
    "#\n",
    "  locr:London a so:City ;\n",
    "    rdfs:label \"London\" .\n",
    "  locr:Liverpool a so:City ;\n",
    "    rdfs:label \"Liverpool\" .\n",
    "  locr:Manchester a so:City ;\n",
    "    rdfs:label \"Manchester\" .\n",
    "  locr:Wolverhampton a so:City ;\n",
    "    rdfs:label \"Wolverhampton\" .\n",
    "  locr:Leicester a so:City ;\n",
    "    rdfs:label \"Leicester\" .\n",
    "  locr:Brighton a so:City ;\n",
    "    rdfs:label \"Brighton\" .\n",
    "  locr:Southampton a so:City ;\n",
    "    rdfs:label \"Southampton\" .\n",
    "  locr:Newcastle a so:City ;\n",
    "    rdfs:label \"Newcastle\" .\n",
    "  locr:Sheffield a so:City ;\n",
    "    rdfs:label \"Sheffield\" .\n",
    "  locr:Birmingham a so:City ;\n",
    "    rdfs:label \"Birmingham\" .\n",
    "  locr:Watford a so:City ;\n",
    "    rdfs:label \"Watford\" .\n",
    "  locr:Norwich a so:City ;\n",
    "    rdfs:label \"Norwich\" .\n",
    "  locr:Bournemouth a so:City ;\n",
    "    rdfs:label \"Bournemouth\" .\n",
    "  locr:Burnley a so:City ;\n",
    "    rdfs:label \"Burnley\" .   \n",
    "\n",
    "#\n",
    "# Leagues\n",
    "#\n",
    "socr:EPL a soco:League ;\n",
    "  rdfs:label \"English Premier League\" ;\n",
    "  soco:nickname \"EPL\"\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Delete or view the entire data set\n",
    "This cell can be used to view the entire graph or as needed, delete it. To delete the data comment out the `CONSTRUCT` line and uncomment the `DELETE` one. Before deleting the data please ensure you do not have any other data loaded that uses the same `PREFIX` as that will also get deleted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql --expand-all\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "PREFIX so: <https://schema.org/>\n",
    "\n",
    "CONSTRUCT { ?s ?p ?o} \n",
    "#DELETE { ?s ?p ?o} \n",
    "\n",
    "WHERE \n",
    "{ ?s ?p ?o\n",
    "  FILTER (strstarts(str(?s),str(socr:)) || \n",
    "          strstarts(str(?s),str(soco:)) || \n",
    "          strstarts(str(?s),str(loco:)) || \n",
    "          strstarts(str(?s),str(so:))   || \n",
    "          strstarts(str(?s),str(locr:)))\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How many teams were in the league that season?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "\n",
    "SELECT (COUNT(*) AS ?count)\n",
    "\n",
    "WHERE\n",
    "{\n",
    "  ?t a soco:Team\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Team info\n",
    "Find the teams in the graph."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "prefix so: <https://schema.org/>\n",
    "\n",
    "SELECT ?team ?founded ?nick\n",
    "\n",
    "WHERE \n",
    "{\n",
    "    ?s a soco:Team .\n",
    "    ?s soco:teamName ?team .\n",
    "    ?s soco:nickname ?nick .\n",
    "    ?s soco:founded ?founded\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Team and stadium info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>\n",
    "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
    "PREFIX owl: <http://www.w3.org/2002/07/owl#>\n",
    "PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>\n",
    "\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "prefix so: <https://schema.org/>\n",
    "\n",
    "SELECT ?team ?nickname ?stadium ?city ?founded\n",
    "\n",
    "WHERE \n",
    "{\n",
    "    ?s a soco:Team .\n",
    "    ?s rdfs:label ?team .\n",
    "    ?s soco:founded ?founded .\n",
    "    ?s soco:nickname ?nickname .\n",
    "    ?s soco:homeStadium ?hs .\n",
    "    ?hs rdfs:label ?stadium .\n",
    "    ?hs so:location ?hcity .\n",
    "    ?hcity rdfs:label ?city\n",
    "\n",
    "}\n",
    "order by ?team\n",
    "#order by asc(?founded)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Building a visualization of the graph\n",
    "The two cells allow a visualization of the result to be displayed. Run a cell and select the Graph tab to see the visualization that was just created. The various hints you can provide and settings that you can adjust when building visualizations are explained in the blog post located [here](https://aws.amazon.com/blogs/database/visualize-query-results-using-the-amazon-neptune-workbench/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Construct a graph of the teams that can be displayed visually\n",
    "The click on a vertex representing a team and select the details view to see more."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "prefix so:   <https://schema.org/>\n",
    "        \n",
    "CONSTRUCT {?s ?p ?o}\n",
    "WHERE {\n",
    "  ?s ?p ?o .\n",
    "  ?s a soco:Team \n",
    "} "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### As before but with all properties expanded as nodes in the visualization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql --expand-all\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "prefix so:   <https://schema.org/>\n",
    "        \n",
    "CONSTRUCT {?s ?p ?o}\n",
    "WHERE {\n",
    "  ?s ?p ?o .\n",
    "  ?s a soco:Team \n",
    "} "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Additional queries\n",
    "\n",
    "### Was Coventry in the league that season?\n",
    "An example of the `OPTIONAL` modifier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "\n",
    "SELECT ( COALESCE(?name,\"Not found\") as ?nickname)\n",
    "WHERE {\n",
    "    OPTIONAL {socr:Coventry soco:nickname ?name } \n",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "prefix so:   <https://schema.org/>\n",
    "        \n",
    "ASK {socr:Coventry a soco:Team}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using FILTER to find sub-strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
    "PREFIX so: <https://schema.org/>\n",
    "\n",
    "SELECT ?city\n",
    "WHERE {\n",
    "    ?s a so:City .\n",
    "    ?s rdfs:label ?city\n",
    "    FILTER contains(?city,\"ou\")\n",
    "}\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find everything the graph contains related to Arsenal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "\n",
    "DESCRIBE socr:Arsenal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How many stadiums are in each city?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "\n",
    "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "PREFIX so: <https://schema.org/>\n",
    "\n",
    "SELECT ?city (count(?city) as ?count)\n",
    "WHERE {\n",
    "    ?s a soco:Stadium .\n",
    "    ?s so:location ?c .\n",
    "    ?c rdfs:label ?city\n",
    "}\n",
    "GROUP BY ?city\n",
    "ORDER BY desc(?count)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Teams and their home cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "\n",
    "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "PREFIX so: <https://schema.org/>\n",
    "        \n",
    "SELECT ?team ?city\n",
    "WHERE {\n",
    "    ?s soco:homeStadium/so:location ?o .\n",
    "    ?s rdfs:label ?team .\n",
    "    ?o rdfs:label ?city\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What year did each stadium open?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>\n",
    "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
    "PREFIX owl: <http://www.w3.org/2002/07/owl#>\n",
    "PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>\n",
    "\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "prefix so: <https://schema.org/>\n",
    "\n",
    "SELECT ?stadium ?opened ?city\n",
    "\n",
    "WHERE \n",
    "{\n",
    "    ?s a soco:Stadium .\n",
    "    ?s rdfs:label ?stadium .\n",
    "    ?s soco:opened ?opened .\n",
    "    ?s so:location ?loc .\n",
    "    ?loc rdfs:label ?city\n",
    "\n",
    "}\n",
    "ORDER BY ?opened"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of teams founded in a given year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "prefix so:   <https://schema.org/>\n",
    "        \n",
    "SELECT  ?year (count(?year) as ?count)\n",
    "WHERE {\n",
    "    ?s a soco:Team .\n",
    "    ?s rdfs:label ?name .\n",
    "    ?s soco:founded ?year\n",
    "}\n",
    "group by ?year\n",
    "order by desc(?count)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What cities had teams in the league that season?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "prefix so:   <https://schema.org/>\n",
    "        \n",
    "SELECT DISTINCT ?city\n",
    "\n",
    "WHERE {\n",
    "    ?s a soco:Stadium .\n",
    "    ?s so:location ?c .\n",
    "    ?c rdfs:label ?city\n",
    "\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Teams founded in a given year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "prefix so:   <https://schema.org/>\n",
    "        \n",
    "SELECT ?year (GROUP_CONCAT(?team;separator=\" and \") AS ?teams)\n",
    "\n",
    "WHERE {\n",
    "  ?s a soco:Team ;\n",
    "     soco:founded ?year ;\n",
    "     rdfs:label ?team\n",
    "} \n",
    "GROUP BY ?year\n",
    "ORDER BY ?year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using `VALUES` to find specific teams"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sparql\n",
    "PREFIX loco: <http://www.example.com/location/ontology/>\n",
    "PREFIX locr: <http://www.example.com/location/resource#>\n",
    "PREFIX soco: <http://www.example.com/soccer/ontology/>\n",
    "PREFIX socr: <http://www.example.com/soccer/resource#>\n",
    "prefix so:   <https://schema.org/>\n",
    "        \n",
    "SELECT ?s ?p ?o\n",
    "WHERE {\n",
    "  VALUES ?team {\"Arsenal\" \"Bournemouth\" \"Norwich City\" \"Chelsea\" \"West Ham United\"}\n",
    "  ?s ?p ?o .\n",
    "  ?s rdfs:label ?team .\n",
    "  ?s a soco:Team\n",
    "} "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Page Width\n",
    "If you would like Jupyter to maximise the horizontal screen real estate run the cell below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
